package com.liang.dao.user;

import com.liang.dao.BaseDao;
import com.liang.pojo.Role;
import com.liang.pojo.User;
import com.mysql.cj.util.StringUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserDaoImpl implements UserDao {
    /**
     * 用户登录
     *
     * @param connection
     * @param userCode
     * @return
     */
    @Override
    public User getLoginUser(Connection connection, String userCode) {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        User user = null;

        if (connection != null) {
            String sql = "select * from smbms_user where userCode=?";
            Object[] params = {userCode};
            try {
                rs = BaseDao.execute(connection, preparedStatement, rs, sql, params);

                if (rs.next()) {
                    user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUserCode(rs.getString("userCode"));
                    user.setUserName(rs.getString("userName"));
                    user.setUserPassword(rs.getString("userPassword"));
                    user.setGender(rs.getInt("gender"));
                    user.setBirthday(rs.getDate("birthday"));
                    user.setPhone(rs.getString("phone"));
                    user.setAddress(rs.getString("address"));
                    user.setUserRole(rs.getInt("userRole"));
                    user.setCreatedBy(rs.getInt("createdBy"));
                    user.setCreationDate(rs.getDate("creationDate"));
                    user.setModifyBy(rs.getInt("modifyBy"));
                    user.setModifyDate(rs.getDate("modifyDate"));
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                BaseDao.closeResource(rs, preparedStatement, connection);
            }

        }

        return user;
    }

    /**
     * 修改密码
     *
     * @param connection
     * @param userPassword
     * @param id
     * @return
     */
    @Override
    public Boolean updatePassword(Connection connection, String userPassword, Integer id) {
        Boolean flag = false;
        PreparedStatement pr = null;
        String sql = "update smbms_user set userPassword=? where id=?";
        Object[] params = {userPassword, id};

        try {
            flag = BaseDao.execute(connection, pr, sql, params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            BaseDao.closeResource(null, pr, connection);
        }


        return flag;
    }

    /**
     * 查询用户分页列表
     *
     * @param connection
     * @param userName
     * @param userRole
     * @param currentPageNo
     * @param pageSize
     * @return
     * @throws SQLException
     */
    @Override
    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {

        List<User> userList = new ArrayList<>();
        PreparedStatement pr = null;
        ResultSet rs = null;
        if (connection != null) {
            StringBuffer sql = new StringBuffer();
            sql.append("select u.*, r.roleName from smbms_user as u,smbms_role as r where u.userRole=r.id");
//            参数列表
            List<Object> list = new ArrayList<>();
//            存在用户名查询
            if (!StringUtils.isNullOrEmpty(userName)) {
                sql.append(" and u.userName like ?");
                list.add("%" + userName + "%");
            }
//            存在角色查询
            if (userRole > 0) {
                sql.append(" and r.id = ?");
                list.add(userRole);
            }
//            分页查询
            sql.append(" order by creationDate desc limit ?,?");
            currentPageNo = (currentPageNo - 1) * pageSize;

            list.add(currentPageNo);
            list.add(pageSize);

            Object[] params = list.toArray();
            rs = BaseDao.execute(connection, pr, rs, sql.toString(), params);

            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUserName(rs.getString("userName"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setCreationDate(rs.getDate("creationDate"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setModifyDate(rs.getDate("modifyDate"));
                user.setUserRoleName(rs.getString("roleName"));
//
                userList.add(user);
            }
            BaseDao.closeResource(rs, pr, connection);

        }

        return userList;
    }

    /**
     * 查询用户总数
     *
     * @param connection
     * @param userName
     * @param userRole
     * @return
     */
    @Override
    public int getCount(Connection connection, String userName, int userRole) {
        int total = 0;
        PreparedStatement pr = null;
        ResultSet rs = null;

        if (connection != null) {
            try {
                StringBuffer sql = new StringBuffer();
                List<Object> list = new ArrayList<>();
//                联表查询数量
                sql.append("select count(1) as count from smbms_user as u,smbms_role as r where u.userRole=r.id");
                if (!StringUtils.isNullOrEmpty(userName)) {
                    sql.append(" and u.userName like ?");//模糊查询
                    list.add("%" + userName + "%");
                }
                if (userRole > 0) {
                    sql.append(" and u.userRole=?");
                    list.add(userRole);
                }
                Object[] params = list.toArray();
                rs = BaseDao.execute(connection, pr, rs, sql.toString(), params);
                if (rs.next()) {
                    total = rs.getInt("count");
                }
//                关闭连接
                BaseDao.closeResource(rs, pr, connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        return total;
    }

    /**
     * 删除用户
     * @param connection
     * @param uid
     * @return
     */
    @Override
    public Boolean deleteUser(Connection connection, Integer uid) {
        Boolean flag = false;
        PreparedStatement pr = null;
        String sql = "delete from smbms_user where id =?";
        Object [] params = {uid};

        if(connection !=null){
            try {
                flag = BaseDao.execute(connection, pr, sql, params);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
                BaseDao.closeResource(null,pr,connection);
            }
        }

        return flag;
    }

    /**
     * 根据用户ID获取用户信息
     * @param connection
     * @param uid
     * @return
     */
    @Override
    public User getUserInfoForId(Connection connection, Integer uid) {
        User user = null;
        PreparedStatement pr=null;
        ResultSet rs=null;

        if(connection !=null){
            try {
                String sql = "select u.*,r.roleName as userRoleName from smbms_user as u,smbms_role as r where u.userRole=r.id and u.id = ?";
                Object [] params = {uid};
                rs = BaseDao.execute(connection,pr,rs,sql,params);
                if (rs.next()){
                    User userInfo = new User();
                    userInfo.setId(rs.getInt("id"));
                    userInfo.setUserCode(rs.getString("userCode"));
                    userInfo.setUserName(rs.getString("userName"));
                    userInfo.setGender(rs.getInt("gender"));
                    userInfo.setBirthday(rs.getDate("birthday"));
                    userInfo.setPhone(rs.getString("phone"));
                    userInfo.setAddress(rs.getString("address"));
                    userInfo.setUserRoleName(rs.getString("userRoleName"));
                    userInfo.setUserRole(rs.getInt("userRole"));
                    user = userInfo;
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
                BaseDao.closeResource(rs,pr,connection);
            }
        }



        return user;
    }

    @Override
    public Boolean insertUser(Connection connection, String userCode, String userName, String userPassword, Integer gender, Date birthday, String phone, String address, Integer userRole, Integer createdBy, Date creationDate) {
        Boolean flag = false;
        PreparedStatement pr=null;

        if(connection !=null){
            try {
                String sql = "insert into smbms_user (userCode, userName, userPassword, gender, birthday, phone, address, userRole, createdBy,creationDate) values (?,?,?,?,?,?,?,?,?,?)";
                Object [] params = {userCode, userName, userPassword, gender, birthday, phone, address, userRole, createdBy,creationDate};
                flag = BaseDao.execute(connection, pr, sql, params);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
                BaseDao.closeResource(null,pr,connection);
            }
        }


        return flag;
    }

    @Override
    public Boolean modifyUser(Connection connection, Integer id,String userName, Integer gender, Date birthday, String phone, String address, Integer userRole, Integer modifyBy, Date modifyDate) {
        Boolean flag = false;
        PreparedStatement pr = null;

        try {
            String sql = "update smbms_user set userName=?,gender=?,birthday=?,phone=?,address=?,userRole=?,modifyBy=?,modifyDate=? where id=?";
            Object [] params={userName,gender,birthday, phone,address,  userRole,  modifyBy,  modifyDate,id};
            flag = BaseDao.execute(connection,pr,sql,params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            BaseDao.closeResource(null,pr,connection);
        }


        return flag;
    }
}
